package com.yuyou.tas.report.service.impl;

import org.apache.commons.lang.StringUtils;
import org.springframework.util.CollectionUtils;

import java.util.List;
import java.util.Map;

/**
 * Created by yujin.liu on 2019/2/25.
 */
public class SqlProvider {

    public String findStudentScoreNew(Map<String, Object> params){

        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "      IF(em.`range_type`=0,'标准','自定义') as rangeType,\n" +
                "      ema.`name` AS activityName,\n" +
                "      em.`name` AS examModelName,\n" +
                "      bl.`name` AS baseLevelName,\n" +
                "      br.`name` AS baseTrimesterName,\n" +
                "      em_time_type.name AS examTimeType,\n" +
                "      bc.`name` AS baseSchool,\n" +
                "      bs.`name` AS baseSection,\n" +
                "      em_subj.`name` AS dataSubject,\n" +
                "      ( SELECT\n" +
                "          GROUP_CONCAT(au.username)\n" +
                "        FROM\n" +
                "\t      lesson_class_user_rel lcur\n" +
                "          LEFT JOIN auth_user au ON au.id = lcur.auth_user_id\n" +
                "          WHERE lcur.type=0\n" +
                "          AND lcur.lesson_class_id=lc.id) as teacher,\n" +
                "      lc.`num` AS classNum,\n" +
                "      lc.`name` AS className,\n" +
                "      s.`student_no` AS studentNo,\n" +
                "      s.`name` AS studentName,\n" +
                "      IF(emr.`status`=0,'参考','缺考') as studentStatus,\n" +
                "      IF(emr.`status`=0,emr.`get_score`,'-') AS getScore,\n" +
                "      em.`score` AS score");

        Integer maxSort = null == params.get("maxSort") ? null : (Integer)params.get("maxSort");
        if(null != maxSort && maxSort > 0){
            for(int i = 1; i <= (Integer)params.get("maxSort"); i++){
                sb.append(",cast(IF(emr.`status`=0,SUM(CASE emq.`sort` WHEN ");
                sb.append(i);
                sb.append(" THEN emrq.`get_score` ELSE 0 END ),'-') as char) AS g");
                sb.append(i);
                sb.append(",");
                sb.append(" SUM(CASE emq.`sort` WHEN ");
                sb.append(i);
                sb.append("  THEN emq.`score` ELSE 0 END ) AS s");
                sb.append(i);
            }
        }

        sb.append("    FROM\n" +
                "      `exam_model_report` emr\n" +
                "      LEFT JOIN `exam_model` em ON emr.`exam_model_id`=em.`id`\n" +
                "      LEFT JOIN `base_trimester` br ON em.`base_trimester_id`=br.`id`\n" +
                "      LEFT JOIN `comm_data_config` em_time_type ON em.`data_time_type`=em_time_type.`id`\n" +
                "      LEFT JOIN `base_section` bs ON em.`base_section_id`=bs.`id`\n" +
                "      LEFT JOIN `comm_data_config` em_subj ON em.`data_subject`=em_subj.`id`\n" +
                "      LEFT JOIN `lesson_class` lc ON emr.`lesson_class_id`=lc.`id`\n" +
                "      LEFT JOIN `lesson` l ON lc.`lesson_id` = l.`id`\n" +
                "      LEFT JOIN `base_level` bl ON l.`base_level_id`=bl.`code` AND l.`auth_organization_id`=bl.`auth_organization_id`\n" +
                "      LEFT JOIN `base_school` bc ON lc.`base_school_id`=bc.`id`\n" +
                "      LEFT JOIN `student` s ON emr.`student_id`=s.`id`\n" +
                "      LEFT JOIN `exam_model_report_ques` emrq ON emr.`auth_organization_id`=emrq.`auth_organization_id`\n" +
                "      AND emr.`exam_model_id`=emrq.`exam_model_id`\n" +
                "      AND emr.`lesson_class_id`=emrq.`lesson_class_id`\n" +
                "      AND emr.`student_id`=emrq.`student_id`\n" +
                "      LEFT JOIN `exam_model_ques` emq ON emrq.`exam_model_ques_id`=emq.`id`\n"+
                "	   LEFT JOIN exam_model_activity ema on em.exam_model_activity_id=ema.id"                
        		);
        sb.append(" where 1 = 1 ");


        List<Long> examModelActivityIds = params.get("examModelActivityIds") == null ? null: (List<Long>) params.get("examModelActivityIds");
        if(!CollectionUtils.isEmpty(examModelActivityIds)){
            sb.append(" and em.exam_model_activity_id IN ( '");
            sb.append(StringUtils.join(examModelActivityIds.toArray(),"','"));
            sb.append("' ) ");
        }

        List<Long> baseSchoolIds = params.get("baseSchoolIds") == null ? null: (List<Long>) params.get("baseSchoolIds");
        if(!CollectionUtils.isEmpty(baseSchoolIds)){
            sb.append(" and lc.base_school_id IN ( '");
            sb.append(StringUtils.join(baseSchoolIds.toArray(),"','"));
            sb.append("' ) ");
        }

        Long baseTrimesterId = null == params.get("baseTrimesterId") ? null : (Long) params.get("baseTrimesterId");
        if(null != baseTrimesterId){
            sb.append(" and em.base_trimester_id =  ");
            sb.append(baseTrimesterId);
        }

        List<Long> baseSectionIds = params.get("baseSectionIds") == null ? null: (List<Long>) params.get("baseSectionIds");
        if(!CollectionUtils.isEmpty(baseSectionIds)){
            sb.append(" and em.base_section_id IN ( '");
            sb.append(StringUtils.join(baseSectionIds.toArray(),"','"));
            sb.append("' ) ");
        }

        List<Long> dataSubjectIds = params.get("dataSubjectIds") == null ? null: (List<Long>) params.get("dataSubjectIds");
        if(!CollectionUtils.isEmpty(dataSubjectIds)){
            sb.append(" and em.data_subject IN (");
            sb.append(StringUtils.join(dataSubjectIds.toArray(),","));
            sb.append(") ");
        }

        Boolean isManage = null == params.get("isManage") ? null : (Boolean) params.get("isManage");
        if((null != isManage) && !isManage){
            sb.append(" and lc.id in (select lesson_class_id from lesson_class_user_rel where auth_user_id = ");
            sb.append(params.get("authUserId"));
            sb.append(") ");
        }

        sb.append(" GROUP BY\n" +
        		"      ema.`name`,\n" +
                "      em.`range_type`,\n" +
                "      em.`name`,\n" +
                "      em.`id` ,\n" +
                "      em.`score`,\n" +
                "      bl.`name`,\n" +
                "      br.`name` ,\n" +
                "      em_time_type.name ,\n" +
                "      bc.`name` ,\n" +
                "      bs.`name` ,\n" +
                "      em_subj.`name` ,\n" +
                "      em.`wal` ,\n" +
                "      lc.`name` ,\n" +
                "      lc.`num` ,\n" +
                "      s.`student_no` ,\n" +
                "      s.`name` ,\n" +
                "      emr.`get_score` ,\n" +
                "      emr.`status`\n" +
                "   ORDER BY\n" +
                "      bc.`name` ,\n" +
                "      em_subj.`name`,\n" +
                "      bl.`name`,\n" +
                "      lc.`num`");

        return sb.toString();
    }

}
